# @Project: diablo
# @File: hongde_rewrite_agreement_by_stock_price
# @Time: 2024/3/21 13:23
# @User: Jolly
# @Email: jolly@007vin.com
"""
定时任务：
每日7点半，同步总仓和一线的库存，校验最新库存价和比例定价是否一致，不一致就更新定价
"""

# @Project: diablo
# @File: agreement_company_equal_price
# @Time: 2024/1/26 11:04
# @User: Jolly
# @Email: jolly@007vin.com

"""
小汽配指定商户，全量刷写固定定价；如果当前定价低于库存价，则根据比例重新计算定价并写入
"""

# @Project: diablo
# @File: reright_agreement_price_point
# @Time: 2024/3/14 16:42
# @User: Jolly
# @Email: jolly@007vin.com
"""
矫正总仓和一线库存加价比例
"""

from math import ceil
import datetime
from datetime import timedelta
import time
import threading
import requests
from queue import Queue
from copy import deepcopy

from io import BytesIO
from openpyxl import load_workbook
from pymongo import MongoClient, InsertOne, UpdateOne
from pymongo.errors import ConnectionFailure
import pymysql

from dbutils.pooled_db import PooledDB

lock = threading.Lock()


class InitAgreementPrice:

    def __init__(self):

        # self.recent_update_goods_data = {}
        #
        # self.hongde_main = ["3"]  # 总仓
        #
        # self.hongde_yixian = ["1390f84351fe464b90dcdbf3a164ff24"]  # 一线
        #
        self.main_group = ["3", "1390f84351fe464b90dcdbf3a164ff24"]  # 总仓和一线

        # self.hongde_merchant_ids = ["3",
        #                             "1390f84351fe464b90dcdbf3a164ff24",
        #                             "fc2e72e26a03423c8f8ee8576debf2ec",  # 龙华鑫众奥
        #                             "3fd50208a10c42ac87c6d84bd9f5661f",  # 龙华名捷
        #                             "a62152e4fe1f49d186d23e060f272e65",  # 龙华名诚行
        #                             "1b5044001e05415ab9f19afbeb73b9c7",  # 罗湖德众
        #                             "3d80a9253b8640d4afc1da174a39d085",  # 罗湖名捷
        #                             "230afea849fd489d88aa6dfeab26a3b3",  # 罗湖名诚行
        #                             "8f5992a1d102410e95cd76f70f0cec30",  # 龙岗德众
        #                             "c217b4952279478889397f15d9968735",  # 龙岗名捷
        #                             "84c2ec02335b4f7f8cabcbbda0d133af",  # 龙岗名诚行
        #                             "411e70375f484ae0b7948b326401a205",  # 平湖鑫德众
        #                             "b8b351a60d114d3a8a078ad178eb41c5",  # 平湖名捷
        #                             "f73a74ddc6584457bd573b400e217110",  # 平湖名诚行
        #                             "0e402537190b4677b5d6d8a1c8af0b2f",  # 寮步鑫众奥
        #                             "c7823ee69b7b4ed4ae8ddab3ee0c12c7",  # 寮步路捷
        #                             "92560c123ce347b9806b6756a0d54097",  # 寮步名诚行
        #                             "79f7412227fb4928bb12f2dc11048a11",  # 沙井鑫众奥
        #                             "4b2c95dd10654036909423de19777d5d",  # 沙井名捷
        #                             "920980642c63490abfad211727bbee7d",  # 沙井名诚行
        #                             "e44f3831dede4640b9bdd40eeaabdc26",  # 宝安鑫众奥
        #                             "50ff6b30688d40b48879857847dcc320",  # 宝安名捷
        #                             "192b5c4f311442cbb613ed63a07d8f78",  # 宝安名诚行
        #                             "c982247f26b74166884d0ca2087d5c17",  # 厚街鑫众奥
        #                             "acf15daf375f497390ac274538e3e058",  # 厚街名捷
        #                             "a2f1c6009ca44cbf8b8e6ddb685b5eae",  # 厚街名诚行
        #                             "3546da2f191b4d1cb8979dad8e42593f",  # 塘厦鑫众奥
        #                             "203ffd0f7d3645f4ba59387b6b532308",  # 塘厦名捷
        #                             "cd5ce52f2db34e39bc66e88bc41238b0"  # 塘厦名诚行
        #                            ]

        # 待刷写的公司
        self.cst_merchant_ids = ["19beeb13d6153457bb854eb43356bc3f",  # 广州沃玛拉国际贸易有限公司
                                 "b7d4ac6094c83bd3b03f96f8a2be07f1",  # 广州福亨汽配供应链有限公司
                                 "8e9a81dae9fb424ba7dd6cf08cb9b5c6",  # 嘉士顿
                                 ]

        start_time = time.time()

        # 初始化数据库连接
        # 2.0生产库
        self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 初始化数据库连接
        self.mysql_conn = self.get_mysql_connection()

        # 执行任务
        self.task()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mongo_connection(self, username, password, host, port):
        try:
            # 创建 MongoClient
            client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
                                 maxPoolSize=300,
                                 minPoolSize=1,
                                 maxIdleTimeMS=30000,
                                 waitQueueTimeoutMS=2000)

            # 检测连接是否可用，如果连接不可用则抛出异常
            if not client:
                raise ConnectionFailure("从MongoDB连接池中获取连接失败！")

            return client

        except ConnectionFailure as e:
            # 处理连接失败的异常
            print(f"连接MongoDB失败: {e}")
            return None

    def get_mysql_connection(self):
        maxconnections = 200  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    def check_agreement_exist(self, code, agreement_excel_id, merchant_id):
        """
        检查是否存在定价：
        1.如果存在定价，并且库存价大于定价，则需要用库存价和比例算出定价进行覆盖；如果库存价小于定价，则略过
        2.如果不存在定价，直接写入固定定价
        """

        check = False

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_goods_price"]

                filter_dict = {
                    "code": code,
                    "merchant_id": merchant_id,
                    "agreement_excel_id": agreement_excel_id
                }
                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:

                    """
                        "_id" : ObjectId("65619d53b30aaeb51f5a4f37"),
                        "agreement_excel_id" : NumberInt(106),
                        "merchant_id" : "32298fd42f693deb89526aceab7399a0",
                        "code" : "A0001802609.848",
                        "agreement_price" : NumberInt(64),
                        "price_type" : "percent_add",
                        "price_value" : 0.0,
                        "price_desc" : "¥ = 64.0*100.0%",
                        "user_id" : "0000000000000000004",
                        "price_user" : "价格更变定价",
                        "price_time" : "2023-11-29 06:04:38",
                        "update_time" : "2023-11-29 06:04:38"
                    """
                    docs = conn.find(filter_dict, {'agreement_price': 1, 'price_value': 1, 'price_type': 1})

                    for doc in docs:
                        agreement_price = doc.get("agreement_price", 0)
                        price_value = doc.get("price_value", 0)
                        price_type = doc.get("price_type", "")

                        filter_dict.update({"agreement_price": agreement_price,
                                            "price_value": price_value,
                                            "price_type": price_type})

                    # 删除已有的
                    # conn.delete_many(filter_dict)
                    check = True
                    return True, filter_dict  # 准备做更新操作

        except Exception as e:
            print("获取协议定价存在检测失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

        return check, {}

    def get_merchant_agreement_excel(self, merchant_ids):
        """
        获取商家价格模板列表
        """

        agreement_info_dict = {}

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            hongde_group_str = ''
            for index, data in enumerate(merchant_ids):

                if index == len(merchant_ids) - 1:
                    hongde_group_str = hongde_group_str + ", '" + str(data) + ""
                    break
                else:
                    hongde_group_str = hongde_group_str + ", '" + str(data) + "'"

            hongde_group_str = hongde_group_str[3:]

            sql = f"select merchant_id, id from diablo_excel_agreement where merchant_id in ('{hongde_group_str}') and status=1"

            cursor.execute(sql)

            result = cursor.fetchall()

            # {"3": [], "4": []}

            if len(result) > 0:

                for result_ in result:

                    agreement_list = agreement_info_dict.get(result_[0])

                    if agreement_list:
                        exist_list = deepcopy(agreement_list)
                        exist_list.append(result_[1])
                        agreement_info_dict[result_[0]] = exist_list
                    else:
                        agreement_info_dict[result_[0]] = [result_[1]]

        except Exception as e:
            print("获取价格模板信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return agreement_info_dict

    def bulk_operate_agreement_excel(self, bulk_insert_list, bulk_update_list):
        """批量操作 写入、更新等"""

        operate_list = []

        if bulk_insert_list:
            for data in bulk_insert_list:
                operate_list.append(InsertOne(data))

        if bulk_update_list:
            for data in bulk_update_list:
                operate_list.append(UpdateOne(data.get("filter_dict"), data.get("update_dict")))

        # 执行批量更新
        # 获取2.0MongoDB连接
        connection = self.diablo_conn_2_0

        try:
            if connection is not None:
                # 执行数据库操作
                db = connection["inventory_data"]
                conn = db["agreement_goods_price"]
                conn.bulk_write(operate_list)

        except Exception as e:
            print("写入2.0MongoDB数据库单据失败：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    def culculate_price_rate(self, _sale_price, _agreement_price, _price_type):
        """
        计算原定价相比现有商品库存价上涨比例，并返回

        计算新的加价比例
        _sale_price: 库存价
        _agreement_price: 新协议价
        _price_type: 加价类型
        """
        if isinstance(_agreement_price, str):
            _agreement_price = float(_agreement_price)
        if isinstance(_sale_price, str):
            _sale_price = float(_sale_price)
        if _sale_price <= 0:
            return 0
        if _price_type == 'percent_sub':  # 降价
            _price_value = round(1 - _agreement_price / _sale_price, 4)
        elif _price_type == 'percent_add':  # 加价
            _price_value = round(_agreement_price / _sale_price - 1, 4)
        elif _price_type == 'selected_add':  # 定价 加价
            _price_value = round(_agreement_price - _sale_price, 4)
        elif _price_type == 'selected_sub':  # 定价 降价
            _price_value = round(_sale_price - _agreement_price, 4)
        elif _price_type == 'customize':  # 固定 价格
            _price_value = _agreement_price
        else:
            _price_value = 0

        return _price_value

    def public_build_price_desc(self, sale_price, price_type, price_value):
        """拼接加价规则描述"""
        if isinstance(sale_price, str):
            sale_price = float(sale_price)
        if isinstance(price_value, str):
            price_value = float(price_value)
        if price_type == 'percent_sub':  # 降价
            return f'¥ = {sale_price}*{round((1 - price_value) * 100, 2)}%'
        elif price_type == 'percent_add':  # 加价
            return f'¥ = {sale_price}*{round((1 + price_value) * 100, 2)}%'
        elif price_type == 'selected_add':  # 定价 加价
            return f'¥ = {sale_price}+{price_value}'
        elif price_type == 'selected_sub':  # 定价 降价
            return f'¥ = {sale_price}-{price_value}'
        elif price_type == 'customize':  # 固定 价格
            return f'¥ = {price_value}'
        return ''

    def public_build_price_desc2(self, sale_price, price_type, price_value):
        """拼接加价规则描述"""
        if isinstance(sale_price, str):
            sale_price = float(sale_price)
        if isinstance(price_value, str):
            price_value = float(price_value)
        if price_type == 'percent_sub':  # 降价
            return f'¥ = {sale_price}*{round((1 - price_value) * 100, 2)}%'
        elif price_type == 'percent_add':  # 加价
            return f'¥ = {sale_price}*{round((1 + price_value) * 100, 2)}%'
        elif price_type == 'selected_add':  # 定价 加价
            return f'¥ = {sale_price}+{price_value}'
        elif price_type == 'selected_sub':  # 定价 降价
            return f'¥ = {sale_price}-{price_value}'
        elif price_type == 'customize':  # 固定 价格
            return f'¥ = {price_value}'
        return ''

    def public_calculate_price(self, sale_price, price_type, price_value):
        """返回协议计算后的价格"""
        if isinstance(sale_price, str):
            sale_price = float(sale_price)
        if isinstance(price_value, str):
            price_value = float(price_value)
        agreement_price = sale_price
        if price_type == 'percent_sub':  # 降价
            agreement_price = round(sale_price * (1 - price_value))
        elif price_type == 'percent_add':  # 加价
            # 为避免出现库存价因为有小数点向上取值，这里用四舍五入取整
            agreement_price = round(sale_price * (1 + price_value))
        elif price_type == 'selected_add':  # 定价 加价
            agreement_price = ceil(round(sale_price + price_value, 4))
        elif price_type == 'selected_sub':  # 定价 降价
            agreement_price = ceil(round(sale_price - price_value, 4))
        elif price_type == 'customize':  # 固定 价格
            agreement_price = ceil(round(price_value, 4))
        return agreement_price

    # 获取新库存价格
    def get_stock_price(self, code, merchant_id):

        connection = self.diablo_conn_2_0

        try:
            if connection is not None:

                # 执行数据库操作
                db = connection["ddll"]
                conn = db["goods"]

                filter_dict = {
                    "merchant_id": merchant_id,
                    "code": code
                }

                docs = conn.find(filter_dict, {"stock_price": 1})

                docs_count = conn.count_documents(filter_dict)

                if docs_count > 0:

                    for doc in docs:
                        if not doc.get("stock_price"):
                            continue
                        else:
                            return doc
                else:
                    return {}

        except Exception as e:
            print("获取原库存定价失败 - mongo：", e)
        finally:
            # 将连接放回连接池
            connection.close()

    # 写入数据
    def write_data(self, *args):

        goods = args[0]
        stock_related = args[1]
        agreement_excel_id = args[2]

        code = goods.get("code")

        # 获取库存价格
        stock_doc = self.get_stock_price(code, stock_related.get("merchant_id"))
        if not stock_doc:
            print(f"库存价格为空----{stock_related.get('merchant_id')}----{code}")
            return

        stock_price = stock_doc.get("stock_price")
        goods.update({"stock_price": stock_price})

        bulk_insert_list = []
        bulk_update_list = []

        # 检查新表是否存在库存
        check, filter_dict_exist = self.check_agreement_exist(code, agreement_excel_id, stock_related.get("merchant_id"))
        if check:

            filter_dict = {
                "code": filter_dict_exist.get('code'),
                "merchant_id": filter_dict_exist.get('merchant_id'),
                "agreement_excel_id": filter_dict_exist.get('agreement_excel_id')
            }

            # 存在的话就更新回写
            old_agreement_price = filter_dict_exist.get("agreement_price")
            price_type = filter_dict_exist.get("price_type")
            price_value = filter_dict_exist.get("price_value")

            # 根据新的基础价格，乘以price_value，得到新的价格agreement_price
            agreement_price = self.public_calculate_price(goods.get("stock_price"), price_type, price_value)
            price_desc = self.public_build_price_desc2(goods.get("stock_price"), price_type, price_value)

            # 如果根据最新的库存价计算的定价和老定价一致，则无需尽心更新，这个定价已经是最新的了
            if old_agreement_price == agreement_price:
                return

            agreement_price_info = {
                "update_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                "user_id": "0000000000000000003",  # 当前用户id
                "price_user": "库存价变更",  # 当前用户名
                "price_time": datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                "agreement_price": agreement_price,
                "price_type": price_type,
                "price_value": price_value,
                "price_desc": price_desc
            }

            update_dict = {'$set': agreement_price_info}

            # 拼入批量列表中
            update_items = {"filter_dict": filter_dict, "update_dict": update_dict}
            bulk_update_list.append(update_items)

        if len(bulk_update_list) > 0:
            try:
                self.bulk_operate_agreement_excel(bulk_insert_list, bulk_update_list)
            except Exception as e:
                print("批量写入定价记录失败：", e)

    def get_stock_related(self, merchant_ids):
        """
        查找库存关联表查找有效的库存关系
        """
        stock_related_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # hongde_merchant_ids = ["3", "1390f84351fe464b90dcdbf3a164ff24"]
            #
            # hongde_merchant_ids.extend(self.hongde_group)

            merchant_ids_group_str = ''
            for index, data in enumerate(merchant_ids):

                if index == len(merchant_ids) - 1:
                    merchant_ids_group_str = merchant_ids_group_str + ", '" + str(data) + ""
                    break
                else:
                    merchant_ids_group_str = merchant_ids_group_str + ", '" + str(data) + "'"

            merchant_ids_group_str = merchant_ids_group_str[3:]

            sql = f"""select merchant_id, merchant_name, merchant_phone, merchant_company_id, company_name, erp_type,
                         erp_uid, app_key, goods_cst_id from diablo_stock_related where status=1 and merchant_id
                          not in ('{merchant_ids_group_str}') """

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    stock_related_list.append({
                        "merchant_id": result_[0],
                        "merchant_name": result_[1],
                        "merchant_phone": result_[2],
                        "merchant_company_id": result_[3],
                        "company_name": result_[4],
                        "erp_type": result_[5],
                        "erp_uid": result_[6],
                        "app_key": result_[7],
                        "goods_cst_id": result_[8]
                    })

        except Exception as e:
            print("获取库存关联信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return stock_related_list

    def get_stock_related_single(self, merchant_ids):
        """
        查找库存关联表查找有效的库存关系
        """
        stock_related_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            # hongde_merchant_ids = ["3", "1390f84351fe464b90dcdbf3a164ff24"]
            #
            # hongde_merchant_ids.extend(self.hongde_group)

            merchant_ids_group_str = ''
            for index, data in enumerate(merchant_ids):

                if index == len(merchant_ids) - 1:
                    merchant_ids_group_str = merchant_ids_group_str + ", '" + str(data) + ""
                    break
                else:
                    merchant_ids_group_str = merchant_ids_group_str + ", '" + str(data) + "'"

            merchant_ids_group_str = merchant_ids_group_str[3:]

            sql = f"""select merchant_id, merchant_name, merchant_phone, merchant_company_id, company_name, erp_type,
                         erp_uid, app_key, goods_cst_id from diablo_stock_related where status=1 and merchant_id
                          in ('{merchant_ids_group_str}') """

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    stock_related_list.append({
                        "merchant_id": result_[0],
                        "merchant_name": result_[1],
                        "merchant_phone": result_[2],
                        "merchant_company_id": result_[3],
                        "company_name": result_[4],
                        "erp_type": result_[5],
                        "erp_uid": result_[6],
                        "app_key": result_[7],
                        "goods_cst_id": result_[8]
                    })

        except Exception as e:
            print("获取库存关联信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return stock_related_list

    def get_cst_goods(self, cst_merchant_id):
        """
        根据厂商通id查找所有商品数据
        """

        # 测试环境
        # url = "http://192.168.191.131:8900/v1/api/openapi/goods/xqp/alllist"
        # 正式环境
        url = "http://192.168.191.245:9960/v1/api/openapi/goods/xqp/alllist"

        headers = {
            "Content-Type": "application/json"
        }

        params = {
            'yc_id': 'xqp0000000000000000001',
            'merchant_id': cst_merchant_id
        }
        response = requests.get(url=url, headers=headers, params=params)
        resp = response.json()

        if resp.get('code') != 1:
            print("厂商通id查找所有商品数据失败")

        return resp['data']

    # 任务执行
    def task(self):
        q = Queue(maxsize=250)  # 设定最大队列数和线程数

        count = 1

        # 获取总仓和一线，近一个小时更新的商品列表
        # self.get_recent_update_goods_data()

        # hongde_merchant_ids = ["3", "1390f84351fe464b90dcdbf3a164ff24"]
        # hongde_merchant_ids.extend(self.hongde_group)

        # 获取所有宏德库存记录
        # stock_related_list = self.get_stock_related(self.hongde_merchant_ids)

        stock_related_list = self.get_stock_related_single(self.main_group)

        for stock_related in stock_related_list:

            goods_cst_id = stock_related.get("goods_cst_id")

            # 获取库存商品列表
            goods_list = self.get_cst_goods(goods_cst_id)

            # 获取所有价格模板
            # agreement_info_dict = self.get_merchant_agreement_excel([stock_related.get("merchant_id")])
            # {'3': [33, 67]}

            agreement_excel_id = 33

            for goods in goods_list:

                t = threading.Thread(target=self.write_data, args=(goods, stock_related, agreement_excel_id))
                q.put(t)
                if q.full() == True:
                    thread_list = []
                    while q.empty() == False:
                        t = q.get()
                        thread_list.append(t)
                        t.start()
                    for t in thread_list:
                        t.join()

                print("当前写入数：", count,
                      ", 商家为：", stock_related.get("merchant_name"),
                      ", 商品编码为：", goods.get("code"),
                      ", 名称为：", goods.get("part_name"))

                count += 1

            # 处理残余队列数据
            thread_list = []
            while q.empty() == False:
                t = q.get()
                thread_list.append(t)
                t.start()
            for t in thread_list:
                t.join()


if __name__ == "__main__":
    InitAgreementPrice()